﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using LibHuman;
namespace Human
{
	/// <summary>
	/// Summary description for frmDSDenghiCSBHXH.
	/// </summary>
	public class frmDSDenghiCSBHXH : System.Windows.Forms.Form
	{
		private AccessData  mDB=new AccessData ();
		private DataSet ds =new DataSet();
        Language lan = new Language();
        string s_bophan = "", user_dn = "";
        private System.Windows.Forms.Button butThoat;
        private System.Windows.Forms.Button butXem;
		private System.Windows.Forms.Label label1;
        private GroupBox groupBox1;
        private RadioButton radMH;
        private RadioButton radMI;
        private RadioButton radExcel;
        private MaskedTextBox maskeddenngay;
		private System.ComponentModel.Container components = null;

		public frmDSDenghiCSBHXH(string s_makp,string s_userdn)
		{
			InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
            s_bophan = s_makp;
            user_dn = s_userdn;
		}
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(frmDSDenghiCSBHXH));
            this.butThoat = new System.Windows.Forms.Button();
            this.butXem = new System.Windows.Forms.Button();
            this.label1 = new System.Windows.Forms.Label();
            this.groupBox1 = new System.Windows.Forms.GroupBox();
            this.radMH = new System.Windows.Forms.RadioButton();
            this.radMI = new System.Windows.Forms.RadioButton();
            this.radExcel = new System.Windows.Forms.RadioButton();
            this.maskeddenngay = new System.Windows.Forms.MaskedTextBox();
            this.groupBox1.SuspendLayout();
            this.SuspendLayout();
            // 
            // butThoat
            // 
            this.butThoat.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.butThoat.ForeColor = System.Drawing.Color.DarkBlue;
            this.butThoat.Image = ((System.Drawing.Image)(resources.GetObject("butThoat.Image")));
            this.butThoat.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.butThoat.Location = new System.Drawing.Point(127, 103);
            this.butThoat.Name = "butThoat";
            this.butThoat.Size = new System.Drawing.Size(92, 27);
            this.butThoat.TabIndex = 24;
            this.butThoat.Text = "Thoát";
            this.butThoat.Click += new System.EventHandler(this.butThoat_Click);
            // 
            // butXem
            // 
            this.butXem.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.butXem.ForeColor = System.Drawing.Color.DarkBlue;
            this.butXem.Image = ((System.Drawing.Image)(resources.GetObject("butXem.Image")));
            this.butXem.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.butXem.Location = new System.Drawing.Point(32, 103);
            this.butXem.Name = "butXem";
            this.butXem.Size = new System.Drawing.Size(92, 27);
            this.butXem.TabIndex = 22;
            this.butXem.Text = "  &Thực hiện";
            this.butXem.Click += new System.EventHandler(this.butXem_Click);
            // 
            // label1
            // 
            this.label1.ForeColor = System.Drawing.Color.DarkBlue;
            this.label1.Location = new System.Drawing.Point(23, 25);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(56, 13);
            this.label1.TabIndex = 18;
            this.label1.Text = "Đến ngày:";
            this.label1.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // groupBox1
            // 
            this.groupBox1.Controls.Add(this.radMH);
            this.groupBox1.Controls.Add(this.radMI);
            this.groupBox1.Controls.Add(this.radExcel);
            this.groupBox1.ForeColor = System.Drawing.Color.DarkBlue;
            this.groupBox1.Location = new System.Drawing.Point(22, 51);
            this.groupBox1.Name = "groupBox1";
            this.groupBox1.Size = new System.Drawing.Size(208, 40);
            this.groupBox1.TabIndex = 25;
            this.groupBox1.TabStop = false;
            // 
            // radMH
            // 
            this.radMH.Checked = true;
            this.radMH.Location = new System.Drawing.Point(10, 14);
            this.radMH.Name = "radMH";
            this.radMH.Size = new System.Drawing.Size(69, 17);
            this.radMH.TabIndex = 0;
            this.radMH.TabStop = true;
            this.radMH.Text = "Màn hình";
            this.radMH.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // radMI
            // 
            this.radMI.Location = new System.Drawing.Point(82, 14);
            this.radMI.Name = "radMI";
            this.radMI.Size = new System.Drawing.Size(57, 17);
            this.radMI.TabIndex = 1;
            this.radMI.Text = "Máy In";
            this.radMI.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // radExcel
            // 
            this.radExcel.Location = new System.Drawing.Point(146, 14);
            this.radExcel.Name = "radExcel";
            this.radExcel.Size = new System.Drawing.Size(51, 17);
            this.radExcel.TabIndex = 1;
            this.radExcel.Text = "Excel";
            this.radExcel.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.radExcel.CheckedChanged += new System.EventHandler(this.radExcel_CheckedChanged);
            // 
            // maskeddenngay
            // 
            this.maskeddenngay.Location = new System.Drawing.Point(81, 22);
            this.maskeddenngay.Mask = "00/00/0000";
            this.maskeddenngay.Name = "maskeddenngay";
            this.maskeddenngay.Size = new System.Drawing.Size(106, 20);
            this.maskeddenngay.TabIndex = 26;
            // 
            // frmDSDenghiCSBHXH
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(244, 149);
            this.Controls.Add(this.maskeddenngay);
            this.Controls.Add(this.groupBox1);
            this.Controls.Add(this.butThoat);
            this.Controls.Add(this.butXem);
            this.Controls.Add(this.label1);
            this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
            this.Name = "frmDSDenghiCSBHXH";
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
            this.Text = "Danh sách cấp sổ BHXH";
            this.Load += new System.EventHandler(this.frmDSDenghiCSBHXH_Load);
            this.groupBox1.ResumeLayout(false);
            this.ResumeLayout(false);
            this.PerformLayout();

		}
		#endregion
        string s_mmyy = "";
		private void frmDSDenghiCSBHXH_Load(object sender, System.EventArgs e)
		{
            DateTime dt = DateTime.Now, dt1;
            dt1 = dt.AddYears(1);
            maskeddenngay.Text = dt.Day.ToString().PadLeft(2, '0') + "/" + dt.Month.ToString().PadLeft(2, '0') + "/" + dt.Year.ToString();

		}
		private void print(int prn)
		{
            string m_user = "";
            m_user = mDB.user;
            s_mmyy = maskeddenngay.Text.Trim().Substring(3, 2) + maskeddenngay.Text.Trim().Substring(8, 2);
            DataSet dstem = new DataSet();
            ExportToExcel exel = new ExportToExcel();
            s_mmyy = maskeddenngay.Text.Trim().Substring(3, 2) + maskeddenngay.Text.Trim().Substring(8, 2);
            try
			{				
                string sql = " select  ";
                sql += " trim(l.ho)||' '||trim(l.ten) as Hoten,l.manv, ";
                sql += " case when l.phai='Nam' then namsinh else null end as Nam_ns, ";
                sql += " case when l.phai='Nữ' then namsinh else null end as Nu_ns, ";
                sql += " '"+mDB.Tenbv+"' tenbv,'"+mDB.Diachi()+"' as diachi,'"+mDB.Syte() +"' as cqql,'"+mDB.Dienthoai()+"' as dienthoai, ";
                sql += " cd.ten as Chucdanh_nghenghiep,to_char(hd.ngaybhxh,'dd/mm/yyyy') as NgaydongBHXH,  ";
                sql += " extract(month from age(to_timestamp('"+ maskeddenngay.Text+"','dd-mm-yyyy'),hd.ngaybhxh)) as thangdd, extract(year from age(to_timestamp('"+maskeddenngay.Text+"','dd-mm-yyyy'),hd.ngaybhxh)) as namdd, ";
                sql += " l.cmndso as SoCMND,l.noicapcmnd as Noicap,to_char(now(),'dd') as Ngay_hh, ";
                sql += " to_char(now(),'mm') as Thang_hh,to_char(now(),'yyyy') as Nam_hh,hd.sobhxh,hd.ghichu as Ghichu ";
                sql += " FROM " + m_user + ".llnv l inner join " + m_user + ".hopdong hd on hd.manv=l.manv ";
                sql += " left join " + m_user + ".dmcd cd on cd.ma=l.macd ";
                sql += " inner join "+m_user+".max_lan ml on hd.manv=ml.manv";
                sql += " WHERE (trim(hd.sobhxh) is null or trim(hd.sobhxh)='') and (hd.ngaynghi is null) and date_part('year',hd.ngaybhxh) <= date_part('year',to_timestamp('"+maskeddenngay.Text+"','dd-mm-yyyy')) ";
                sql += " and hd.lan=ml.lan";
                sql += " order by l.ten ";
                ds=mDB.get_data(sql);
                ds.WriteXml("..\\xml\\h_DSLDDenghiCapBHXH_bl.xml", XmlWriteMode.WriteSchema);
				if(ds.Tables[0].Rows.Count!=0)
				{
                    switch (prn)
                    {
                        case 1: frmReport f = new frmReport(mDB, ds, lan.Change_language_MessageText("DANH SÁCH LAO ĐỘNG ĐỀ NGHỊ CẤP SỔ BHXH"), "h_DSLDDenghiCapBHXH_bl.rpt", true, user_dn);
                                f.ShowDialog(this);
                                break;
                       case 3: dstem = ket_xuat_exel(ds);
                               exel.exp_exel_dsdndongbh(dstem, "dsdndongbh",s_mmyy );
                               break;
                    }					
				}
				else 
					MessageBox.Show(lan.Change_language_MessageText("Chưa có dữ liệu"),AccessData.Msg);
			}
			catch{}
		}
        private DataSet ket_xuat_exel(DataSet dst)
        {
            string[] str = new string[] { "STT", "Họ và Tên", "Nam", "Nữ", "Số", "Nơi Cấp", "Chức Danh Ngành Nghề", "Tháng", "Năm", "Số Sổ BHYT", "Ghi Chú" };
            DataSet dst1 = new DataSet();
            dst1.Tables.Add();
            dst1.Tables[0].Columns.Add("stt", typeof(string));
            dst1.Tables[0].Columns.Add("Hoten", typeof(string));
            dst1.Tables[0].Columns.Add("Nam_ns", typeof(string));
            dst1.Tables[0].Columns.Add("Nu_ns", typeof(string));
            dst1.Tables[0].Columns.Add("SoCMND", typeof(string));
            dst1.Tables[0].Columns.Add("Noicap", typeof(string));
            dst1.Tables[0].Columns.Add("Chucdanh_nghenghiep", typeof(string));
            dst1.Tables[0].Columns.Add("thangdd", typeof(string));
            dst1.Tables[0].Columns.Add("namdd", typeof(string));
            dst1.Tables[0].Columns.Add("sobhxh", typeof(string));
            dst1.Tables[0].Columns.Add("Ghichu", typeof(string));

            DataRow nrow;
            int stt = 0;
            
            foreach (DataRow r in dst.Tables[0].Rows)
            {
                stt++;
                nrow = dst1.Tables[0].NewRow();
                nrow["stt"] = stt;
                //nrow["Hoten"] = mDB.Caps(r["Hoten"].ToString().ToLower());
                nrow["Hoten"] = r["Hoten"].ToString();
                nrow["Nam_ns"] = r["Nam_ns"].ToString();
                nrow["Nu_ns"] = r["Nu_ns"].ToString();
                nrow["SoCMND"] = r["SoCMND"].ToString();
                nrow["Noicap"] = r["Noicap"].ToString();
                nrow["Chucdanh_nghenghiep"] = r["Chucdanh_nghenghiep"].ToString();
                nrow["thangdd"] = r["thangdd"].ToString();
                nrow["namdd"] = r["namdd"].ToString();
                nrow["sobhxh"] = r["sobhxh"].ToString();
                nrow["Ghichu"] = r["Ghichu"].ToString();
                dst1.Tables[0].Rows.Add(nrow);
            }
            return dst1;
        }
        private void butXem_Click(object sender, System.EventArgs e)
		{
            try
            {           
                if (radMH.Checked == true) print(1);
                if (radMI.Checked == true) print(2);
                if (radExcel.Checked==true) print(3);
            }
            catch
            {
            }
		}

		private void butThoat_Click(object sender, System.EventArgs e)
		{			
			this.Close();
		}

		private void butIn_Click(object sender, System.EventArgs e)
		{
			//print(true);
		}
        private void radExcel_CheckedChanged(object sender, EventArgs e)
        {

        }
	}
}
